In the context of database systems, a “dimension” is a list of values that provide categories for data. A dimension acts as an index for identifying values of a variable. For example, if sales data has a separate sales figure for each month, then the data has a MONTH dimension. That is, the data is organized by month. A dimension is similar to a key in a relational database. Data that is organized by two or more dimensions is referred to as “multidimensional data”.
Any item of data within a multidimensional variable can be uniquely and completely selected by specifying one member from each of the variable's dimensions. For example, if a sales measure is dimensioned by MONTH, PRODUCT, and MARKET, specifying “January” for the MONTH dimension, “Stereos” for the PRODUCT dimension, and “Eastern Region” for the MARKET dimension uniquely specifies a single value of a measure. A multidimensional variable can be conceptually thought of as an N-dimensional array, where N is the number of dimensions of the variable, and where each value in the array may be accessed by specifying one dimension key value for each dimension (e.g. MDVar(dim1, dim2, dim3, . . . , dimN)). Thus, dimensions offer a concise and intuitive way of organizing and selecting data for retrieval, updating, and performing calculations.
Multidimensional arrays with 2 and 3 dimensions may be depicted visually as grids and cubes, respectively. For convenience, it has become customary to refer to the conceptual multidimensional arrays that correspond to multidimensional variables as “multidimensional cubes” (or simply “cubes”) regardless of how many dimensions they possess. Hence, a multidimensional data cube is a set of n-dimensional data objects. Further, each multidimensional value is said to belong to a “cell” of the cube, where the address of the cell is the set of dimension key values (one per dimension) that correspond to the multidimensional value contained therein.
For the purpose of explanation, the multidimensional value that belongs to a cell shall be referred to as the “cell value” of that cell. Cell values may include base measure values, where a measure is a type of data (e.g., sales), or aggregated measure values, such as a summation, average, minimum value, maximum value, and the like. For example, a cell might contain a value representing a summation of sales in dollars for a particular product in a particular time period in a particular market, where “sales” is the measure and summation is the aggregation function.
Multidimensional data may be stored in relational database management systems (“RDBMS”) or in specialized, “multidimensional” database management systems (“MDDBMS”). MDDBMSs provide structures and access techniques specifically designed for multidimensional data, and therefore provide relatively efficient storage and access to multidimensional data. However, when stored in specialized MDDBMSs, only applications that are specially built to interact with those systems are able to access and manipulate the data. This is because the commands sent to the MDDBMSs have to conform to the command language supported by the MDDBMS.
Typically, multidimensional data is associated with Online Analytical Processing (OLAP), which allows users to analyze different dimensions of multidimensional data. Often, OLAP is used for data warehousing and, at times, data mining. The command languages of MDDBMSs with OLAP capabilities typically provide native support for specifying and executing many different OLAP DML operations, i.e., functions or operations that can be used to manipulate multidimensional data.
When multidimensional data is stored in RDBMSs, all applications that support interaction with relational databases have access to the data. Such database applications communicate with the RDBMS by submitting commands that conform to the database language supported by the RDBMS, the most common of which is the ANSI Structured Query Language (SQL). However, the RDBMSs and SQL do not have native support for many of the OLAP DML operations.
According to one approach, multidimensional data may be stored in a MDDBMS, but accessed by sending SQL to a RDBMS. In this approach, the RDBMS interacts with the MDDBMS to access the data from the MDDBMS. Unfortunately, because the operations are initially specified in SQL to the RDBMS, the native capabilities of the MDDBMS to perform OLAP operations are underutilized.
Based on the foregoing, there is room for improvement in techniques for invoking OLAP operations on multidimensional data through a RDBMS. Specifically, there is room for improvement in techniques for accessing results of calculations involving multidimensional data using the SQL.